using System;
using System.Collections.Generic;
using System.Text;
using System.Configuration;
using System.Data.SqlClient;
using System.Data;

namespace Prototype
{
    public class Room
    {
        private string number;
        private RoomType roomType;

        public string Number
        {
            get { return number; }
        }

        public int Capacity
        {
            get
            {
                return roomType.Capacity;
            }
            
        }

        public string RoomType
        {
            get
            {
               return  roomType.Name;
            }
           
        }

        public decimal RoomPrise
        {
            get
            {
                return roomType.Prise;
            }
          
        }

        private Room(string number,RoomType type) 
        {
            this.number = number;
            this.roomType = type;
        }



        public static void InsertRoom(string number, RoomType roomType)
        {
            string str = ConfigurationManager.ConnectionStrings[0].ToString();
            string commandString=
                "declare @id int set @id=(select RoomtypeID from RoomType r where [Name]=@Name) Insert into Rooms(Number,RoomTypeID) values(@Number,@id)";
            
            SqlConnection conn = new SqlConnection(str);
            conn.Open();
            try
            {
                SqlParameter rType = new SqlParameter("@Name", SqlDbType.NChar);
                SqlParameter num = new SqlParameter("@Number", SqlDbType.NChar);
                rType.Value = roomType.Name;
                num.Value = number;

                SqlCommand commandInsert = new SqlCommand(commandString);
                commandInsert.Parameters.Add(rType);
                commandInsert.Parameters.Add(number);
                commandInsert.ExecuteNonQuery();
            }
            finally { conn.Close(); }
 
        }

        public static void ChangeRoom(string number, RoomType newRoomType)
        {
            string str = ConfigurationManager.ConnectionStrings[0].ToString();
            string commandString =
                "update Rooms SET RoomTypeID=(select RoomTypeID from RoomType where Name=@RoomType) from Rooms where Number=@RoomNumber";
            SqlConnection conn = new SqlConnection(str);
            conn.Open();
            try
            {
                
                SqlParameter rType = new SqlParameter("@RoomType", SqlDbType.Int);
                SqlParameter rNum = new SqlParameter("@RoomNumber", SqlDbType.NChar);
                rType.Value = newRoomType;
                rNum.Value = number;

                SqlCommand commandUpdate = new SqlCommand(commandString);
                commandUpdate.Parameters.Add(rType);
                commandUpdate.Parameters.Add(rNum);
                commandUpdate.ExecuteNonQuery();
            }
            finally { conn.Close(); }
        }

        public static Room[] GetFree()
        {
            string str="Data Source=(local)\\sqlexpress;Initial Catalog=Hotel System;Integrated Security=True";
            //string str = ConfigurationManager.ConnectionStrings["Connection"].ToString();
            string commandString =
                "select r.Number,t.Name,t.Capacity,t.Prise From Rooms r join RoomType t on r.RoomTypeID=t.RoomTypeID where r.RoomID not in (SELECT RoomID From Reservation)";
            SqlConnection conn = new SqlConnection(str);
            SqlCommand commandSelect = new SqlCommand(commandString);
            commandSelect.Connection = conn;
            conn.Open();
            try
            {
                
                SqlDataReader reader=
                    commandSelect.ExecuteReader();
                List<Room> result = new List<Room>();
                using (reader)
                {
                    while (reader.Read())
                    {   RoomType type=new RoomType();
                        type.Name=(string)reader["Name"];
                        type.Prise=(decimal)reader["Prise"];
                        type.Capacity=(int)reader["Capacity"];
                        Room r = new Room((string)reader["Number"], type);
                        result.Add(r);
                    }
                }
                return result.ToArray();
            }
            finally { conn.Close(); }
            

        }
        public static Room GetByNumber(string number) 
        {
            string str = "Data Source=(local)\\sqlexpress;Initial Catalog=Hotel System;Integrated Security=True";
            //string str = ConfigurationManager.ConnectionStrings[0].ToString();
            string commandString =
                "select r.Number,t.Name,t.Capacity,t.Prise From Rooms r,RoomType t where r.RoomTypeID=t.RoomTypeID and Number=@number";
           
            SqlConnection conn = new SqlConnection(str);
            SqlCommand commandSelect = new SqlCommand(commandString);
            commandSelect.Connection = conn;
            conn.Open();
            try
            {
                List<Room> result = new List<Room>();

                
                SqlParameter num = new SqlParameter("@number", SqlDbType.NChar);
                num.Value = number;
                commandSelect.Parameters.Add(num);
                SqlDataReader reader = commandSelect.ExecuteReader();
                using (reader)
                {
                    while (reader.Read())
                    {
                        RoomType type = new RoomType();
                        type.Name = (string)reader["Name"];
                        type.Prise = (decimal)reader["Prise"];
                        type.Capacity = (int)reader["Capacity"];
                        Room r = new Room((string)reader["Number"], type);
                        result.Add(r);
                    }
                }
                return result[0];
            }
            finally { conn.Close(); }

        }
    }
}
